package cn.net.autocode.platform.table.service;

import cn.net.autocode.core.bean.ResultT;
import cn.net.autocode.core.utils.JSONUtils;
import cn.net.autocode.platform.design.webclient.AcWebClient;
import cn.net.autocode.platform.table.ddl.DataBaseDDL;
import cn.net.autocode.platform.table.ddl.DatabaseDDLFactory;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import cn.net.autocode.dbManager.Dao;
import cn.net.autocode.dbManager.IdWorker;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.util.HashMap;
import java.util.Map;

@Service("platformTableServ")
@Slf4j
public class TableServ {
    private final Dao dictDao;

    private final Dao dao;

    private final AcWebClient acWebClient;

    public TableServ(@Qualifier("dictDao") Dao dictDao, Dao dao, AcWebClient acWebClient) {
        this.dictDao = dictDao;
        this.dao = dao;
        this.acWebClient = acWebClient;
    }

    public void category2local(JSONArray serverCategory){
        JSONArray localCategory = dictDao.queryJSONArray("select code,label from ac_table_category");
        Map<String, JSONObject> localMap = new HashMap<>();
        //转化为小写的keys
        for(int i=0;i<localCategory.size();i++){
            JSONObject json = localCategory.getJSONObject(i);
            localMap.put(json.getString("code"),json);
        }

        for(int i=0;i<serverCategory.size();i++){
            JSONObject json = serverCategory.getJSONObject(i);
            String code = json.getString("code");
            String label = json.getString("label");
            if(localMap.containsKey(code)){
                if(!localMap.get(code).getString("label").equals(label)){
                    dictDao.executeUpdate("update ac_table_category set label=? where code=?",label,localMap.get(code).getString("code"));
                }
            }else{
                dictDao.executeUpdate("insert into ac_table_category(code,label) values(?,?)",json.getString("code"),label);
            }
        }
    }

    @Transactional("dictTxManager")
    public void table2local(JSONObject acTable){
        String tableName = acTable.getString("tableName").toLowerCase();
        String code = acTable.getString("code");
        long syncTime = acTable.getLongValue("syncTime");

        JSONObject localTable = dictDao.queryJSON("select id,code,table_name,remarks,order_column,select_column,category_code,sync_time from ac_table where table_name=?",tableName);
        if(localTable.isEmpty()){
            //创建表
            localTable = new JSONObject();
            localTable.put("id", IdWorker.ulid());
            localTable.put("syncTime",0);
            localTable.put("tableName",acTable.getString("tableName"));
            localTable.put("code",acTable.getString("code"));
            localTable.put("remarks",acTable.getString("remarks"));
            localTable.put("categoryCode",acTable.getString("categoryCode"));
            //添加信息
            dictDao.createJSON(localTable,"ac_table");

            //添加列信息
            JSONObject fieldJson = new JSONObject();
            fieldJson.put("id", IdWorker.ulid());
            fieldJson.put("tableId",localTable.getString("id"));
            fieldJson.put("name","id");
            fieldJson.put("type","varchar");
            fieldJson.put("length",36);
            fieldJson.put("scale",0);
            fieldJson.put("isnullable","N");
            fieldJson.put("primaryKey","Y");
            fieldJson.put("sortNo",0);
            dictDao.createJSON(fieldJson,"ac_table_fields");

            try {
                DataBaseDDL ddl= DatabaseDDLFactory.newInstance(dao);
                ddl.createServTable(tableName);
            } catch (Exception e) {
                log.info(e.getMessage());
            }
        }

        //判断时间搓是否一致
        if(syncTime==localTable.getLongValue("syncTime")){
            return;
        }

        //1、同步表
        dictDao.executeUpdate("update ac_table set code=?,remarks=?,order_column=?,select_column=?,category_code=?,sync_time=? where id=?",
                code,acTable.getString("remarks"),acTable.getString("orderColumn"),acTable.getString("selectColumn"),acTable.getString("categoryCode"),syncTime,localTable.getString("id"));

        //2、同步字段
        JSONArray acTableFields = dictDao.queryJSONArray("select id,name,type,length,scale,defaultvalue,isnullable,primary_key,remarks,sort_no,dict_code,prohibit_update " +
                " from ac_table_fields  where table_id=? order by sort_no",localTable.getString("id"));

        Map<String,JSONObject> localFieldMap = new HashMap<>();
        for(int i=0;i<acTableFields.size();i++){
            JSONObject json = acTableFields.getJSONObject(i);
            localFieldMap.put(json.getString("name"),json);
        }

        JSONArray tableFields = acTable.getJSONArray("tableFields");

        for(int i=0;i<tableFields.size();i++){
            JSONObject json = tableFields.getJSONObject(i);
            String key = json.getString("name");
            if(localFieldMap.containsKey(key)){
                boolean checkResult = JSONUtils.compare(json,localFieldMap.get(key));
                if(!checkResult){
                    //记录表不一致
                    dictDao.executeUpdate("update ac_table_fields set " +
                                    "type=?,length=?,scale=?,defaultvalue=?,isnullable=?,primary_key=?,remarks=?,sort_no=?,dict_code=?,prohibit_update=?,asc_desc=? " +
                                    "where id=?", json.getString("type"),json.getIntValue("length"),json.getIntValue("scale"),
                            json.getString("defaultvalue"),json.getString("isnullable"),json.getString("primaryKey"),
                            json.getString("remarks"),json.getIntValue("sortNo"),json.getString("dictCode"),
                            json.getString("prohibitUpdate"),json.getString("ascDesc"),localFieldMap.get(key).getString("id"));
                    //如何更新记录
                }
            }else{
                //创建列
                DataBaseDDL ddl;
                try {
                    dao.queryMap("select "+json.getString("name")+" from "+acTable.getString("tableName")+" where id='-1'");
                } catch (Exception e) {
                    try {
                        ddl = DatabaseDDLFactory.newInstance(dao);
                        ddl.addColumn(json, acTable.getString("tableName"));
                    } catch (Exception ex) {
                        log.info(e.getMessage());
                        log.info(ex.getMessage());
                    }
                }
                json.put("id", IdWorker.ulid());
                json.put("tableId",localTable.getString("id"));
                dictDao.createJSON(json,"ac_table_fields");
            }
        }

        //3、同步子表信息
        JSONArray acTableRef = dictDao.queryJSONArray("SELECT id,pk_field,fk_table,fk_field,delete_rule " +
                "FROM ac_table_ref where table_id=? ",localTable.getString("id"));
        Map<String, JSONObject> localRefMap = new HashMap<>();
        //转化为小写的keys
        for(int i=0;i<acTableRef.size();i++){
            JSONObject json = acTableRef.getJSONObject(i);
            localRefMap.put(json.getString("fkTable")+"@"+json.getString("fkField"),json);
        }
        JSONArray tableRef = acTable.getJSONArray("tableRef");

        //集合信息
        for(int i=0;i<tableRef.size();i++){
            JSONObject json = tableRef.getJSONObject(i);
            String key = json.getString("fkTable")+"@"+json.getString("fkField");
            if(localRefMap.containsKey(key)){
                JSONObject localJson = localRefMap.get(key);
                if(!json.getString("deleteRule").equals(localJson.getString("deleteRule"))||!json.getString("pkField").equals(localJson.getString("pkField"))){
                    dictDao.executeUpdate("update ac_table_ref set pk_field=?,delete_rule=? where id=?",json.getString("pkField"),json.getString("deleteRule"),localJson.getString("id"));
                }
            }else{
                json.put("id",IdWorker.ulid());
                json.put("tableId",localTable.getString("id"));
                dictDao.createJSON(json,"ac_table_ref");
            }
        }

        //4、同步规则信息
        JSONArray acTableRules = dictDao.queryJSONArray("select id,field_name,ref_column,rule_group,rule_type,match_ul,default_value,result_type,sort_no " +
                " from ac_table_rules where table_id=?",localTable.getString("id"));
        Map<String, JSONObject> localRulesMap = new HashMap<>();
        //转化为小写的keys
        for(int i=0;i<acTableRules.size();i++){
            JSONObject json = acTableRules.getJSONObject(i);
            localRulesMap.put(json.getString("fieldName")+"@"+json.getString("ruleType"),json);
        }

        JSONArray tableRules = acTable.getJSONArray("tableRules");
        //集合信息
        for(int i=0;i<tableRules.size();i++){
            JSONObject json = tableRules.getJSONObject(i);

            String key = json.getString("fieldName")+"@"+json.getString("ruleType");
            if(localRulesMap.containsKey(key)){
                boolean checkResult = JSONUtils.compare(json,localRulesMap.get(key));
                if(!checkResult){
                    //记录表不一致
                    dictDao.executeUpdate("update ac_table_rules set remark=?,ref_column=?,match_ul=?,default_value=?,result_type=? where id=?",
                            json.getString("remark"),json.getString("refColumn"),json.getString("matchUl"),json.getString("defaultValue"),json.getString("resultType"),
                            localRulesMap.get(key).getString("id"));
                }
            }else{
                json.put("id",IdWorker.ulid());
                json.put("tableId",localTable.getString("id"));
                dictDao.createJSON(json,"ac_table_rules");
            }
        }
    }


    public ResultT syncToServer(String tableId){
        //获取表信息
        JSONObject acTable = dictDao.queryJSON("select code,table_name,remarks,order_column,select_column,category_code,sync_time from ac_table where id=?",tableId);

        //获取目录信息
        JSONObject tableCategory = dictDao.queryJSON("select * from ac_table_category where code=?",acTable.getString("categoryCode"));

        //获取字段信息
        JSONArray acTableFields = dictDao.queryJSONArray("select t2.table_name,t1.name,t1.type,t1.length,t1.scale,t1.defaultvalue,t1.isnullable,t1.primary_key,t1.remarks,t1.sort_no,t1.dict_code,t1.prohibit_update " +
                "from ac_table_fields t1,ac_table t2 where t1.table_id=t2.id and t2.id=? order by t1.sort_no",tableId);

        //获取子表信息
        JSONArray acTableRef = dictDao.queryJSONArray("SELECT t2.table_name,t1.pk_field,t1.fk_table,t1.fk_field,t1.delete_rule " +
                "FROM ac_table_ref t1,ac_table t2 where t1.table_id=t2.id and t2.id=? ",tableId);


        //获取处理规则
        JSONArray acTableRules = dictDao.queryJSONArray("select t2.table_name,t1.field_name,t1.ref_column,t1.rule_group,t1.rule_type,t1.match_ul,t1.default_value,t1.result_type,t1.sort_no " +
                " from ac_table_rules t1,ac_table t2 " +
                " where t1.table_id=t2.id and t2.id=? " +
                " order by t2.table_name",tableId);

        acTable.put("acTableFields",acTableFields);
        acTable.put("acTableRef",acTableRef);
        acTable.put("acTableRules",acTableRules);
        acTable.put("acTableCategory",tableCategory);
        JSONObject rs = acWebClient.syncToServer(acTable);
        if(rs.getIntValue("code")==1){
            dictDao.executeUpdate("update ac_table set sync_time=?,is_sync='Y' where id=?",rs.getJSONObject("data").getLongValue("syncTime"),tableId);
            return ResultT.success();
        }else{
            if(rs.getString("msg").contains("远程库同步到本地")){
                this.syncFromServer(acTable);
                return ResultT.fail(rs.getString("msg"));
            }else{
                return ResultT.fail(rs.getString("msg"));
            }

        }
    }

    public ResultT syncFromServer(JSONObject param) {

        //同步ac_table_category
        JSONObject r1 = acWebClient.getAcTableCategory();
        if(r1.getIntValue("code")==1){
            JSONArray serverCategory = r1.getJSONArray("data");
            this.category2local(serverCategory);
        }else{
            return ResultT.fail(r1.getString("msg"));
        }
        //同步表
        JSONObject r2 = acWebClient.getAcTable(param);
        if(r2.getIntValue("code")==1){
            JSONArray acTables = r2.getJSONArray("data");
            //1、同步表信息
            for(int i=0;i<acTables.size();i++){
                JSONObject acTable = acTables.getJSONObject(i);
                this.table2local(acTable);
            }
        }else{
            return ResultT.fail(r2.getString("msg"));
        }

        return ResultT.success();
    }
}
